const db = require('../config/database');
const bcrypt = require('bcryptjs');

async function migrate() {
  try {
    const query = db.query;

    // 创建角色表
    await query(`
      CREATE TABLE IF NOT EXISTS roles (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL UNIQUE,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);

    // 创建权限表
    await query(`
      CREATE TABLE IF NOT EXISTS permissions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL UNIQUE,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);

    // 创建角色-权限关联表
    await query(`
      CREATE TABLE IF NOT EXISTS role_permissions (
        role_id INT,
        permission_id INT,
        PRIMARY KEY (role_id, permission_id),
        FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
        FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
      )
    `);

    // 检查是否已存在role_id列
    const checkRoleColumn = await query(`
      SHOW COLUMNS FROM users LIKE 'role_id'
    `);

    // 如果不存在role_id列，则添加
    if (checkRoleColumn.length === 0) {
      await query(`
        ALTER TABLE users 
        ADD COLUMN role_id INT,
        ADD CONSTRAINT fk_user_role 
        FOREIGN KEY (role_id) REFERENCES roles(id)
      `);
    }

    // 初始化角色数据
    const existingRoles = await query('SELECT * FROM roles');
    if (existingRoles.length === 0) {
      await query(`
        INSERT INTO roles (name, description) VALUES 
        ('super_admin', '超级管理员，拥有所有权限'),
        ('admin', '管理员，拥有大部分系统管理权限'),
        ('project_manager', '项目经理，可管理项目和团队'),
        ('team_member', '普通团队成员，有限的系统访问权限')
      `);
    }

    // 初始化权限数据
    const existingPermissions = await query('SELECT * FROM permissions');
    if (existingPermissions.length === 0) {
      await query(`
        INSERT INTO permissions (name, description) VALUES 
        ('user_management', '用户管理权限'),
        ('system_settings', '系统设置权限'),
        ('project_create', '创建项目权限'),
        ('project_edit', '编辑项目权限'),
        ('project_delete', '删除项目权限'),
        ('performance_view', '查看绩效权限'),
        ('risk_management', '风险管理权限')
      `);
    }

    // 为超级管理员角色分配所有权限
    const rolePermissionsCount = await query('SELECT COUNT(*) as count FROM role_permissions');
    if (rolePermissionsCount[0].count === 0) {
      await query(`
        INSERT INTO role_permissions (role_id, permission_id)
        SELECT 1, id FROM permissions
      `);
    }

    // 更新现有用户的角色（假设第一个用户是超级管理员）
    const salt = await bcrypt.genSalt(10);
    const hashedPassword = await bcrypt.hash('admin123', salt);

    // 检查是否已存在超级管理员用户
    const existingAdminUser = await query('SELECT * FROM users WHERE id = 1');
    if (existingAdminUser.length === 0) {
      await query(`
        INSERT INTO users (username, email, password, role_id) VALUES 
        ('admin', 'admin@project-tracker.com', ?, 1)
      `, [hashedPassword]);
    } else {
      // 如果用户已存在，更新其角色为超级管理员
      await query(`
        UPDATE users SET role_id = 1 WHERE id = 1
      `);
    }

    console.log('数据库迁移成功：角色和权限系统已初始化');
    process.exit(0);
  } catch (error) {
    console.error('数据库迁移失败:', error);
    process.exit(1);
  }
}

migrate();
